package com.houdoudou.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import com.houdoudou.bean.Box;
import com.houdoudou.bean.BoxUsage;
import com.houdoudou.bean.Order;
import com.houdoudou.common.Constant;
import com.houdoudou.common.DateTimeFunction;


public class BoxUsageDao {
	  //必须要设置为 不自动提交
	 Connection conn=null;	
		public BoxUsageDao(Connection conn) {
			super();
			this.conn = conn;
			
		}
		
		public String  getIdleBox( Order order,int boxStateAfter) throws Exception{
		       String boxesIn=null;
		      
			// STEP1,检查是否该日已经有赋值		
			
				String searchSql="select * from boxUsage where machine= "+ order.getMachineID()
						+"  and dateAA= "+ order.getDateAA()+ " and timeFlag= "+order.getTimeFlag()
						+" and boxState= 1" ; //空闲
				System.out.println(" boxUsage searchSql= "+searchSql);
				List<BoxUsage> boxUsages= search(searchSql);
				 if(boxUsages.size()<=0){ //该班次并未设置。
			    	initOneShipBox(order);
					boxUsages= search(searchSql);
				 }	
				 
			  int boxNumOfMachine=new MachineDao(conn).selectById(order.getMachineID()).getBoxNum();
			  int boxNumReserved=(int)Math.floor(boxNumOfMachine*Constant.percentageOfBoxReserved);
			   
			     if(boxUsages.size()< boxNumReserved +order.getBoxNum()){ //箱格数量不够, 清理箱格
			    	 
			    	 int boxNeedCleared=boxNumReserved +order.getBoxNum()-boxUsages.size();
				     String dateTimeNow= DateTimeFunction.getMyDateTime(0-Constant.minutesCouldAutoCancelOrder);
				     String searchClearSql="select * from boxUsage where machine= "+ order.getMachineID()
								+"  and dateAA= "+ order.getDateAA()+ " and timeFlag= "+order.getTimeFlag()
								+" and boxState= 2 and dateTimePL < "+dateTimeNow +" group by orderID,dateTimePL order by dateTimePL "; 
				     System.out.println(" boxUsage searchClearSql= "+searchClearSql);
				     List<BoxUsage> ocupiedBoxUsage = search(searchClearSql);
				     for(int i=0;i<ocupiedBoxUsage.size();i++){
				      Order orderExist= new OrderDao(conn).selectById(ocupiedBoxUsage.get(i).getOrderID());
				      if(orderExist.getState()==1) {//未支付 
				    		Order orderUpdate= new Order();
				    		orderUpdate.setId(orderExist.getId());
				    		orderUpdate.setState(3); 
				    		orderUpdate.setDateTimeRF(DateTimeFunction.myDateFormat(new Date())); 
				    		new OrderDao(conn).update(OrderDao.getUpdateSql(orderUpdate));
				    		updateBoxState(orderExist, 1);
				    		
				    		 // 这里要通知消费者。
				    		
				    		conn.commit();
				    		boxNeedCleared-=orderExist.getBoxesIn().length()/3;
				    		if(boxNeedCleared<=0) break;
				    	  }
				      } 
				     boxUsages= search(searchSql); // 清理完后，重新搜索
			     }	     
			     
                 if(boxUsages.size()< boxNumReserved +order.getBoxNum()){ //箱格数量不够       	 
					 return null;
				 }else{ //箱格数量够了
					 boxesIn="";
					 
					 for(int i=0;i< order.getBoxNum();i++){
						 String boxStr=boxUsages.get(i).getBox()+"";
						while(boxStr.length()<3){
							boxStr="0"+boxStr;
						}
						 boxesIn+= boxStr;
						String updateSql= "update boxUsage set boxState= "+boxStateAfter+" where machine= "+boxUsages.get(i).getMachine()+" and box="+boxUsages.get(i).getBox()
								+" and dateAA="+boxUsages.get(i).getDateAA()+ " and timeFlag=" +boxUsages.get(i).getTimeFlag() ; 
						System.out.println(" boxUsage updateSql= "+updateSql);
						Statement stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
						stm.executeUpdate(updateSql);		
						conn.commit();  // 这里可能有问题
					 }
					 return boxesIn;
				 }
			 
			
	
		}
		public void  updateOrderId(Order order) throws SQLException{
			String boxesIn= order.getBoxesIn();
			for(int i=0;i<boxesIn.length()/3;i++){
				int box= Integer.parseInt(boxesIn.substring(i*3, (i+1)*3));
				
				String updateSql= "update boxUsage set orderID = "+order.getId()+" ,dateTimePL= "+ order.getDateTimePL()+" where machine= "+order.getMachineID()+" and box="+box
						+" and dateAA="+order.getDateAA()+ " and timeFlag=" +order.getTimeFlag() ; 
				System.out.println(" boxUsage updateSql= "+updateSql);
				Statement stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
				stm.executeUpdate(updateSql);
			}					
			
		}
		
		public void  updateBoxState(Order order,int boxState) throws SQLException{
			String boxesIn= order.getBoxesIn();
			for(int i=0;i<boxesIn.length()/3;i++){
				int box= Integer.parseInt(boxesIn.substring(i*3, (i+1)*3));			
				String updateSql= "update boxUsage set boxState = "+boxState+" where machine= "+order.getMachineID()+" and box="+box
						+" and dateAA="+order.getDateAA()+ " and timeFlag=" +order.getTimeFlag() ; 
				System.out.println(" boxUsage updateSql= "+updateSql);
				Statement stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
				stm.executeUpdate(updateSql);
			}					
		
		}
		
		private boolean initOneShipBox(Order order) throws SQLException{
			boolean result=true;
			List<Box>  boxes;
			 Box boxSample= new Box();
			 boxSample.setMachine(order.getMachineID());
			
			    boxes= new BoxDao(conn).search(BoxDao.getSearchSql(boxSample));
			
			for (Iterator iterator = boxes.iterator(); iterator.hasNext();) {
				Box box = (Box) iterator.next();
				BoxUsage boxUsage= new BoxUsage();
				boxUsage.setOwner(box.getOwner());
				boxUsage.setMachine(box.getMachine());
				boxUsage.setBox(box.getId());
				boxUsage.setBoxState(box.getState());  
				boxUsage.setDateAA(order.getDateAA());
				boxUsage.setTimeFlag(order.getTimeFlag());
					create(boxUsage);			
			}
		    	conn.commit();
			
			return result;
		} 
		
		
		
		private  void create( BoxUsage boxUsage ) throws SQLException{	
			long result=0;
			  PreparedStatement pstm=null; 
			   Statement  stm=null;
			   ResultSet rs=null; 	
			   String sql=null;
		        try {
		        	sql = "insert  into boxUsage ( machine,box,boxState,orderID,owner,dateAA,dateTimePL,timeFlag) " //8个
		        + "values "
		        + "(?,?,?,?,?,?,?,?)";
		        	//共8个参数。
		        	pstm=conn.prepareStatement(sql);
		        	pstm.setInt(1, boxUsage.getMachine());
		        	pstm.setInt(2, boxUsage.getBox());
		        	pstm.setInt(3, boxUsage.getBoxState());  
		        	pstm.setLong(4, boxUsage.getOrderID()); 
		        	pstm.setString(5, boxUsage.getOwner());  
		        	pstm.setString(6, boxUsage.getDateAA());
	        			   
		        	pstm.setString(7, boxUsage.getDateTimePL());
		        	pstm.setInt(8, boxUsage.getTimeFlag());  
		        	
		        	pstm.executeUpdate();
		        	
				} finally{
					ConnFactory.closeStatment(pstm, pstm, rs);
				}			
			
		}
		
		public List< BoxUsage> search( String sql) throws SQLException{	
			List< BoxUsage> boxUsages= new ArrayList<BoxUsage>();
			  PreparedStatement pstm=null; 
			   Statement  stm=null;
			   ResultSet rs=null; 		
		        try {        		        	
					stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
					rs=stm.executeQuery(sql);
					while(rs.next()){
						BoxUsage boxUsage = new BoxUsage();
					
						boxUsage.setBox(rs.getInt("box"));
						boxUsage.setMachine(rs.getInt("machine"));
			        	boxUsage.setBoxState(rs.getInt("boxState"));
			        	boxUsage.setOrderID(rs.getLong("orderID"));  
			        
			        	boxUsage.setOwner(rs.getString("owner"));
			        	boxUsage.setDateAA(rs.getString("dateAA"));
			        	boxUsage.setDateTimePL(rs.getString("dateTimePL"));  
			        	boxUsage.setTimeFlag(rs.getInt("timeFlag")); 
			        	
						boxUsages.add(boxUsage);
					}
				} finally{
					ConnFactory.closeStatment(pstm, pstm, rs);
				}			
			return boxUsages;
		}	
		
}
